package com.Impl;

import com.entity.Book;
import com.service.CRUDBook;
import com.utils.JDBCUtil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

public class BookCRUD implements CRUDBook {
    private QueryRunner queryRunner = new QueryRunner();
    private Connection connection;
    @Override
    public Long queryBooksCount() {
         connection = JDBCUtil.getConnection();
         String sql = "select count(bookId) from books";
        try {
            return queryRunner.query(connection, sql, new ScalarHandler<Long>());
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public int addBook(Book book) {
        connection = JDBCUtil.getConnection();

        String sql = "insert into books (bookId,bookName,bookCategory,author,publisher,price)values(?,?,?,?,?,?)";
        int bookId = book.getBookId();
        String bookName = book.getBookName();
        String bookCategory = book.getBookCategory();
        String author = book.getAuthor();
        String publisher = book.getPublisher();

        double price = book.getPrice();

        try {
            int row = queryRunner.update(connection,sql,bookId,bookName,bookCategory,author,publisher,price);
            return row;

        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
//    根据ID删除图书
    @Override
    public int deleteBook(int bookId) {
        connection = JDBCUtil.getConnection();
        String sql = "delete from books where bookId=?";
        try {
            int update = queryRunner.update(connection, sql, bookId);
            return update;

        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

    }

    @Override
    public int updateBook(Book book) {

        connection = JDBCUtil.getConnection();
        String sql = "update books set bookName=?,bookCategory=?,author=?,publisher=?,price=? where bookId=?" ;
        try {
            int update = queryRunner.update(connection, sql, book.getBookName()
                    , book.getBookCategory()
                    , book.getAuthor()
                    , book.getPublisher()
                    , book.getPrice()
                    , book.getBookId());
            return update;


        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public int clearAllBooks() {

        connection = JDBCUtil.getConnection();
        String sql = "delete from books" ;
        System.out.println("开始清空");
        try {
            int deleteCount = queryRunner.update(connection, sql);
            System.out.println("清空完成");
            return deleteCount;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public List<Book> queryBooksByPage(int startIndex, int pageSize) {
        connection = JDBCUtil.getConnection();
        String sql = "select bookId,bookName,bookCategory,author,publisher,price from books limit ?,?" ;
        try {
            return queryRunner.query(connection, sql, new BeanListHandler<Book>(Book.class), startIndex, pageSize);

        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}
